KWAN的解忧杂货铺

# 1.innodb 概述?

InnoDB 存储引擎支持事务，其设计目标主要面向在线事务处理(OLTP)的应用。其特点是行锁设计、支持外键，并支持类似于 Oracle 的非锁定读，即默认读取操作不会产生锁。从 MySQL 数据库 5.5.8 版本开始，InnoDB 存储引擎是默认的存储引擎。innodb 将数据放在一个独立的表空间，索引和数据一起存储在表独立的 idb 文件中.

InnoDB 通过使用多版本并发控制 (MVCC) 来获得高并发性，并且实现了 SQL 标准的 4 种隔离级别，默认为 REPEATABLE 级别。同时，使用一种被称为 next-key locking 的策略来避免幻读(phantom）现象的产生。除此之外，InnoDB 储存引擎还提供了插入缓冲 (insert buffer)、二次写 (double write）、自适应哈希索引(adaptive hash index）、预读 (read ahead）等高性能和高可用的功能。

对于表中数据的存储，InnoDB 存储引擎采用了聚集 (clustered）的方式，因此每张表的存储都是按主键的顺序进行存放。如果没有显式地在表定义时指定主键，InnoDB 存储引擎会为每一行生成一个6 字节的 ROWID，并以此作为主键。

# 2.InnoDB 的关键特性?

  • 插入缓冲(Insert Buffer)
  • 两次写(Double Write)
  • 自适应哈希索引(Adaptive Hash Index)
  • 异步 IO(Async IO)
  • 刷新邻接页(Flush Neighbor Page)
  • 行锁设计
  • 高并发性 MVCC

# 3.innodb 和 myisam 的区别

MyISAM:MyISAM 引擎是 MySQL5.5.8 及之前版本的默认引擎，它的特点是:

  • 不支持行锁，读取时对需要读到的所有表加锁，写入时则对表加排它锁，不支持事务，不支持外键
  • 不支持崩溃后的安全恢复
  • 在表有读取查询的同时，支持往表中插入新纪录
  • 支持 BLOB 和 TEXT 的前 500 个字符索引，支持全文索引。支持延迟更新索引，极大提升写入性能
  • 对于不会进行修改的表，支持压缩表，极大减少磁盘空间占用
  • 存储数据使用 MYD(数据文件)和 MYI(索引文件)

InnoDB:InnoDB 在 MySQL5.5.8 后成为默认引擎，它的特点是:

  • 支持行锁,也支持表锁，默认为行级锁
  • 采用 MVCC 来支持高并发，支持事务，支持外键
  • 支持崩溃后的安全恢复。
  • 使用 idb 文件存储索引和数据

# 4.表锁和行锁的区别?

数据即索引,索引即数据,行锁的实现是依赖于 innodb 存储引擎的索引设计,并且与记录锁,间隙锁,临键锁等都有一定的关系。本质来说,锁的还是索引。

在 MySQL 的 InnoDB 存储引擎中，表锁和行锁是两种不同的锁机制，用于控制并发访问数据库中的数据。它们在锁定粒度和底层实现上有一些区别。

  1. 表锁（Table Locks）：

    • 锁定粒度： 表锁是对整张表进行锁定，意味着当一个事务获取了表锁后，其他事务无法对该表进行任何读写操作，即使是不涉及锁定行的操作。
    • 适用场景： 表锁适用于少量的、相对稳定的数据访问模式，或者当需要执行大规模的数据维护操作时，可以快速获取整张表的锁来保护数据完整性。
    • 底层实现： InnoDB 存储引擎中的表锁是通过在数据字典中设置一个标志来实现的。这种锁定方式效率较低，因为它会导致大量并发事务之间的阻塞。

  2. 行锁（Row Locks）：

    • 锁定粒度： 行锁是针对表中的单行数据进行锁定，允许多个事务在同一时刻同时访问表的不同行。
    • 适用场景： 行锁适用于高并发的数据访问模式，其中只有少数行会被同时访问，这可以减少并发事务之间的竞争，提高系统的并发性能。
    • 底层实现： InnoDB 存储引擎实现行锁的方式是通过在每一行数据的存储上增加两个隐藏的字段，记录了这行数据的锁信息。这使得 InnoDB 能够在仅锁定需要的行时避免不必要的锁竞争，从而提高了并发性。

行锁相对于表锁具有更细粒度的控制能力，能够更好地支持高并发的访问模式。然而，行锁也可能引入一些额外的开销，如死锁的风险和锁管理的复杂性。因此，在选择使用表锁还是行锁时，需要根据具体的应用场景和性能需求进行权衡。在 InnoDB 存储引擎中，默认情况下会使用行级锁定来支持更好的并发控制。

# 5.什么是插入缓冲?

InnoDB 存储引擎开创性地设计了 Insert Buffer，对于非聚集索引的插入或更新操作，不是每一次直接插入到索引页中，而是先判断插入的非聚集索引页是否在缓冲池中，若在，则直接插入;若不在，则先放入到一个 Insert Buffer 对象中，好似欺骗。数据库这个聚集的索引已经插到叶子节点，而实际并没有，只是存放在另一个位置。然后再以一定的频率和情况进行Insert Buffer和辅助索引叶子节点的 merge(合并)操作，这时通常能将多个插入合并到一个操作中(因为在一个索引页中)，这就大大提高了对于非聚集索引插入的性能。

然而 Insert Buffer 的使用需要同时满足以下两个条件:

  • 索引是辅助索引(secondary index)，聚集索引是唯一的，不合适

  • 索引不是唯一(unique)的，因为如果是唯一的，需要校验将要插入的数据是否唯一，性能较低.

当满足以上两个条件时，InnoDB 存储引擎会使用 Insert Buffer，这样就能提高插入操作的性能了。

辅助索引不能是唯一的，因为在插入缓冲时，数据库并不去查找索引页来判断插入的记录的唯一性。如果去查找肯定又会有离散读取的情况发生，从而导致 Insert Buffer 失去了意义。

# 6.插入缓冲的实现

Insert Buffer 的数据结构是一棵 B+树。在 MySQL4.1 之前的版本中每张表有一棵 Insert Buffer B+树。而在现在的版本中，全局只有一棵 Insert Buffer B+树，负责对所有的表的辅助索引进行 Insert Buffer。而这棵 B+树存放在共享表空间中，默认也就是 ibdata1 中。

非叶节点存放的是查询的 search key(键值)，其构造如图所示。

image-20240126151216417

search key 一共占用 9 个字节，其中 space 表示待插入记录所在表的表空间 id，在 InnoDB 存储引擎中，每个表有一个唯一的 space id 可以通过 space id 查询得知是哪张表。space 占用 4 字节。marker 占用 1 字节，它是用来兼容老版本的 Insert Buffer。offset 表示页所在的偏移量，占用 4 字节。

当一个辅助索引要插入到页(space，offset)时，如果这个页不在缓冲池中，那么 InnoDB 存储引擎首先根据上述规则构造一个 search key，接下来查询Insert Buffer这棵 B+树，然后再将这条记录插入到 Insert Buffer B+树的叶子节点中。 对于插入到 Insert Buffer B+树叶子节点的记录(如图 2-4 所示)，并不是直接将待插入的记录插入，而是需要根据如下的规则进行构造:

image-20230823002605896 space、marker、offset 字段和之前非叶节点中的含义相同，一共占用 9 字节。第 4 个字段 metadata 占用 4 字节，其存储的内容如表 2-2 所示。

image-20230823002551622

IBUF_REC_OFFSET_COUNT 是保存两个字节的整数，用来排序每个记录进入 Insert Buffer 的顺序。因为从 InnoDB1.0.x 开始支持 Change Buffer，所以这个值同样记录进入 Insert Buffer 的顺序。通过这个顺序回放(replay)才能得到记录的正确值。从 Insert Buffer 叶子节点的第 5 列开始，就是实际插入记录的各个字段了。因此较之原插入记录，Insert Buffer B+树的叶子节点记录需要额外 13 字节的开销。

因为启用 Insert Buffer 索引后，**辅助索引页(space，page_no)**中的记录可能被插入到 Insert Buffer B+树中，所以为了保证每次 Merge Insert Buffer 页必须成功，还需要有一个特殊的页用来标记每个辅助索引页(space，page_no)的可用空间。这个页的类型为 Insert Buffer Bitmap。每个 Insert Buffer Bitmap 页用来追踪 16384 个辅助索引页，也就是 256 个区(Extent)。每个 Insert Buffer Bitmap 页都在 16384 个页的第二个页中。

每个辅助索引页在 Insert Buffer Bitmap 页中占用 4 位(bit).

image-20231022231328496

# 7.什么是 merge insert buffer?

将 insert buffer 中的数据合并到辅助索引页中.

概括地说，Merge_Insert_Buffer 的操作可能发生在以下几种情况下:

  • 辅助索引页被读取到缓冲池时;

  • Insert Buffer Bitmap 页追踪到该辅助索引页已无可用空间时:

  • Master Thread.

Insert Buffer Bitmap 页用来追踪每个辅助索引页的可用空间，并至少有 1/32 页的空间。若插入辅助索引记录时检测到插入记录后可用空间会小于 1/32 页，则会强制进行一个合并操作，即强制读取辅助索引页，将 Insert Buffer B+树中该页的记录及待插入的记录插入到辅助索引页中。这就是上述所说的第二种情况

# 8.什么是 Change Buffer?

Change Buffer（变更缓冲区）是数据库引擎中的一种优化技术，用于提高数据修改操作（如更新或删除）的性能。类似于 Insert Buffer（插入缓冲区），Change Buffer 也是用于减少磁盘写入操作，从而提高数据库的写入性能。

当进行数据修改操作时，数据库引擎需要在磁盘中定位到对应的数据页（page），然后进行修改并将修改后的数据写回磁盘。在高并发的写入操作场景中，频繁的磁盘写入操作可能成为性能瓶颈。为了减少这种开销，数据库引擎引入了 Change Buffer。

Change Buffer 的工作原理如下：

  1. 当进行更新或删除操作时，引擎会将数据的修改操作暂时存储在 Change Buffer 中，而不是直接写回磁盘。
  2. Change Buffer 是一个内存中的数据结构，它存储了待修改的数据页（page）中发生的变更信息，例如需要更新的数据行和更新后的值，或者需要删除的数据行的标识。
  3. 当 Change Buffer 达到一定大小或者满足一定条件时，数据库引擎将会把 Change Buffer 中的修改操作合并到对应的数据页上，并将合并后的数据页写回磁盘。这个过程通常在后台进行，不会阻塞当前的写入操作。

对一条记录进行 delete 操作可能分为两个过程:

  • 将记录标记为已删除;
  • 真正将记录删除。

因此 Delete Buffer 对应 UPDATE 操作的第一个过程，即将记录标记为删除。Purge Buffer 对应 UPDATE 操作的第二个过程，即将记录真正的删除。同时，InnoDB 存储引擎提供了参数 innodb_change_buffering，用来开启各种 Buffer 的选项。该参数可选的值为:inserts、deletes、purges、changes、allnone。 inserts、deletes purges 就是前面讨论过的三种情况。changes 表示启用 inserts 和 deletes，all 表示启用所有，none 表示都不启用。该参数默认值为 all。

innodb_change_buffer_max_size 来控制 ChangeBuffer 最大使用内存的数量:

innodb_change_buffermax_size 值默认为 25，表示最多使用 1/4 的缓冲池内存空间。而需要注意的是，该参数的最大有效值为 50，最大使用 1/2 的缓冲池内存空间.

# 9.什么是二次写?

关于 IO 的最小单位：

  • 数据库 IO 的最小单位是 16K(MySQL 默认，oracle 是 8K)
  • 文件系统 IO 的最小单位是 4K(也有 1K 的)
  • 磁盘 IO 的最小单位是 512 字节

因此，存在 IO 写入导致 page 损坏的风险

image-20231021173727643

如果说 Insert Buffer 带给 InnoDB 存储引擎的是性能上的提升，那么 double write(两次写)带给 InnoDB 存储引擎的是数据页的可靠性。当发生数据库宕机时，可能 InnoDB 存储引擎正在写入某个页到表中，而这个页只写了一部分，比如 16KB 的页，只写了前 4KB，之后就发生了宕机，这种情况被称为部分写失效(partial page write)。

在应用(apply)重做日志前，用户需要一个页的副本，当写入失效发生时，先通过页的副本来还原该页，再进行重做，这就是 double write。在 InnoDB 存储引擎中 double write 的体系架构如图

image-20231022231345537

double write 由两部分组成，一部分是内存中的 double write buffer，大小为 2MB，另一部分是物理磁盘上共享表空间中连续的 128 个页，即 2 个区(extent)，大小同样为 2MB。在对缓冲池的脏页进行刷新时，并不直接写磁盘，而是会通过 memcpy 函数将脏页先复制到内存中的 double write buffer，之后通过 double write buffer 再分两次，每次 1MB 顺序地写入共享表空间的物理磁盘上，然后马上调用 fsync 函数，同步磁盘，避免缓冲写带来的问题。在这个过程中，因为 double write 页是连续的，因此这个过程是顺序写的，开销并不是很大。在完成 double write 页的写入后，再将 double write buffer 中的页写入各个表空间文件中，此时的写入则是离散的。

如果操作系统在将页写入磁盘的过程中发生了崩溃，在恢复过程中，InnoDB 存储引擎可以从共享表空间中!的 double write 中找到该页的一个副本，将其复制到表空间文件，再应用重做日志。

# 10.什么是自适应哈希索引?

哈希(hash)是一种非常快的查找方法，在一般情况下这种查找的时间复杂度为 O(1)，即一般仅需要一次查找就能定位数据。

InnoDB 存储引擎会监控对表上各索引页的查询。如果观察到建立哈希索引可以带来速度提升，则建立哈希索引，称之为自适应哈希索引(Adaptive Hash Index AHI)。AHI 是通过缓冲池的 B+树页构造而来，因此建立的速度很快，而且不需要对整张表构建哈希索引。InnoDB 存储引擎会自动根据访问的频率和模式来自动地为某些热点页建立哈希索引。

  • AHI 有一个要求，即对这个页的连续访问模式必须是一样的。访问模式一样指的是查询的条件一样，若交替进行上述两种查询
  • 以该模式访问了 100 次
  • 页通过该模式访问了 N 次，其中 N=页中记录*1/16

自适应哈希索引采用哈希表的方式实现。不同的是，这仅是数据库自身创建并使用的，DBA 本身并不能对其进行干预。自适应哈希索引经哈希函数映射到一个哈希表中，因此对于字典类型的查找非常快速，如

SELECT * FROM TABLE WHERE index_col='xxx';
1

但是对于范围查找就无能为力了。通过命令可以看到当前自适应哈希索引的使用状况

SHOW ENGINE INNODB STATUS;
1

哈希索引只能用来搜索等值的查询

# 11.什么是刷新邻接页?

InnoDB 存储引擎还提供了 Flush Neighbor Page(刷新邻接页)的特性。

其工作原理为:当刷新一个脏页时，InnoDB 存储引擎会检测该页所在区(extent)的所有页，如果是脏页，那么一起进行刷新。这样做的好处显而易见，通过 AIO 可以将多个 IO 写入操作合并为一个 IO 操作，故该工作机制在传统机械磁盘下有着显著的优势。机械硬盘建议启用该特性，而对于固态硬盘有着超高 IOPS 性能的磁盘，则建议将该参数设置为 0，即关闭此特性。

参数 innodb_flush_neighbors，用来控制是否启用该特性。

# 12.什么是 MRR 优化?

MySQL5.6 版本开始支持 Multi-Range Read(MRR)优化。Multi-Range Read 优化的目的就是为了减少磁盘的随机访问，并且将随机访问转化为较为顺序的数据访问。

MRR 优化是数据库中的一种优化技术，全称为 "Multi-Range Read Optimization"（多范围读取优化）。它是用于改善数据库查询性能的一种方法，特别适用于处理范围查询（Range Query）操作。

范围查询是指根据某个范围条件（例如：日期范围、数值范围等）来检索数据库中的数据。执行范围查询时，数据库引擎需要定位到满足条件的多个数据行，并将这些数据行返回给查询请求。在高并发或大规模数据量的情况下，范围查询可能会导致性能下降，因为需要大量的磁盘访问和数据检索操作。

MRR 优化通过改进数据的访问方式，使得范围查询的性能得到提升。它的主要原理如下：

  1. 数据排序：MRR 优化首先会对查询的数据进行排序，以确保它们在物理存储上是连续的或者相邻的。这种排序可以减少随机磁盘访问的次数，从而提高范围查询的效率。
  2. 批量读取：MRR 优化将范围查询的结果数据按照较大的块（例如页或者块）批量读取到内存中，而不是单个数据行一个接一个地读取。这样可以减少磁盘访问的次数，提高数据读取的效率。
  3. 并行处理：MRR 优化还可以通过并行处理的方式来加速范围查询。它可以将查询结果分成多个子任务，并同时处理这些子任务，从而更好地利用多核处理器的计算能力。

# 13.什么是 ICP 优化?

ICP 优化是数据库查询中的一种优化技术，全称为 "Index Condition Pushdown"（索引条件下推）。它是用于改善数据库查询性能的一种方法，特别适用于复杂的查询操作，涉及多个条件和多个索引的情况。

  • 禁用 ICP 时，存储引擎会通过遍历索引定位基表中的行，然后返回给 Server 层，再去为这些数据行进行 where 条件的过滤。

  • 启用 ICP 时，如果 where 条件可以使用索引，MySQL 会把这部分过滤操作放到存储引擎层，存储引擎通过索引过滤，把满足的行从表中读取出。 ICP 可以减少存储引擎必须访问基表的次数以及 Server 曾必须访问存储引擎的次数。

和 Multi-Range Read 一样，Index Condition Pushdown 同样是 MySQL5.6 开始支持的一种根据索引进行查询的优化方式。之前的 MySQL 数据库版本不支持 Index Condition Pushdown，当进行索引查询时，首先根据索引来查找记录，然后再根据 WHERE 条件来过滤记录。在支持 Index Condition Pushdown 后，MySQL 数据库会在取出索引的同时，判断是否可以进行 WHERE 条件的过滤，也就是将 WHERE 的部分过滤操作放在了存储引擎层。在某些查询下，可以大大减少上层 SQL 层对记录的索取(fetch)，从而提高数据库的整体性能。

Index Condition Pushdown 优化支持 range、ref、eq_ref 、ref_or_null 类型的查询，当前支持 MyISAM 和 InnoDB 存储引擎。当优化器选择 Index Condition Pushdown 优化时，可在执行计划的列 Extra 看到 Using index condition 提示。

# 14.ICP 的限制条件?

ICP（Index Condition Pushdown）优化在数据库查询中是一个有用的技术，但是它也有一些限制条件，这些限制条件可能影响它的适用性和效果。以下是一些常见的 ICP 优化的限制条件：

  1. 范围条件：ICP 优化主要适用于等值条件，即对于索引列的查询条件是等于某个值的情况。对于范围条件（如大于、小于、区间等），ICP 优化的效果可能较差，因为范围条件的处理通常需要在数据层级进行，无法完全下推到索引层级。
  2. 多列条件：ICP 优化通常对于多列条件的处理有限。当查询涉及多个条件，并且这些条件之间没有简单的逻辑关系时，ICP 优化的效果可能不明显。
  3. 联合索引：对于联合索引，ICP 优化只能应用于最左前缀，即只有查询条件包含联合索引的最左列时才能进行优化。如果查询条件涉及联合索引的非最左列，ICP 优化无法生效。
  4. NULL 值：对于包含 NULL 值的索引列，ICP 优化可能受到限制。由于 NULL 值的特殊性，它的处理可能需要额外的检查和判断，可能无法完全下推到索引层级。
  5. 引用外部表：如果查询涉及到引用外部表的数据，ICP 优化可能无法生效。因为外部表的数据可能需要额外的访问和处理，无法直接下推到索引层级。
  6. 子查询和复杂查询：对于包含子查询或者复杂查询逻辑的情况，ICP 优化可能有限或者无法应用。

尽管 ICP 优化有上述的限制条件，但它在很多情况下仍然是非常有用的优化技术，特别是对于单列等值查询的情况。数据库引擎通常会根据查询的复杂性和数据分布来自动选择是否应用 ICP 优化。同时，通过合理的索引设计和查询优化，可以进一步提高 ICP 优化的效果和数据库查询性能。

# 15.如何避免离散读?

MySQL5.6 之前，优化器在进行离散读决策的时候，如果数据量比较大，会选择使用聚集索引，全表扫描。

MySQL5.6 版本开始支持 Multi-Range Read（MRR）优化。Multi-Range Read 优化的目的就是为了减少磁盘的随机访问，并且将随机访问转化为较为顺序的数据访问，这对于 IO-bound 类型的 SQL 查询语句可带来性能极大的提升。Multi-Range Read 优化可适用于 range，ref，eq_ref 类型的查询。

MRR 优化有以下几个好处：

  • MRR 使数据访问变得较为顺序。在查询辅助索引时，首先根据得到的查询结果，按照主键进行排序，并按照主键排序的顺序进行书签查找。

  • 减少缓冲池中页被替换的次数。（顺序查找可以对一个页进行顺序查找，无需离散加载数据页）

  • 批量处理对键值的查询操作。

  • 对于 InnoDB 和 MyISAM 存储引擎的范围查询和 JOIN 查询操作，MRR 的工作方式如下：

  • 将查询得到的辅助索引键值存放于一个缓存中，这时缓存中的数据是根据辅助索引键值排序的。

  • 将缓存中的键值根据 RowID 进行排序。

  • 根据 RowID 的排序顺序来访问实际的数据文件。

举例说明：

SELECT * FROM salaries WHERE salary＞10000 AND salary＜40000;
1

salary 上有一个辅助索引 idx_s，因此除了通过辅助索引查找键值外，还需要通过书签查找来进行对整行数据的查询。当不启用 Multi-Range Read 特性时，看到的执行计划如图：

image-20231022231403932

若启用 Mulit-Range Read 特性，则除了会在列 Extra 看到 Using index condition 外，还会看见 Using MRR 选项

image-20231022231418884

image-20231022231434875

Multi-Range Read 还可以将某些范围查询， 拆分为键值对， 以此来进行批量的数据查询 。 这样做的好处是可以在 拆分过程中， 直接过滤一些不符合查询条件的数据， 例如：

SELECT * FROM t WHERE key_part1 ＞=1000 AND key_part1 ＜ 2000 AND key_part2=10000;
1

表 t 有（key_part1，key_part2）的联合索引，因此索引根据 key_part1，key_part2 的位置关系进行排序。若没有 Multi-Read Range，此时查询类型为 Range，SQL 优化器会先将 key_part1 大于 1000 且小于 2000 的数据都取出，即使 key_part2 不等于 1000。待取出行数据后再根据 key_part2 的条件进行过滤。这会导致无用数据被取出。如果有大量的数据且其 key_part2 不等于 1000，则启用 Mulit-Range Read 优化会使性能有巨大的提升。

倘若启用了 Multi-Range Read 优化，优化器会先将查询条件进行拆分，然后再进行数据查询。就上述查询语句而言，优化器会将查询条件拆分为（1000，10000），（1001，10000），（1002，10000），…，（1999，10000），最后再根据这些拆分出的条件进行数据的查询。

在非必要的情况下，拒绝使用 select _；在必须 select _ 的情况下，尽量使用 MySQL5.6+的版本开启 MRR；在必须 select * 的情况下且 MySQL 小于 5.6 版本下，可以根据数据量进行离散读和聚集索引两种情况下的性能进行对比，必要时采用 force index 语句强制指定索引。

# 16.说说 purge 操作?

purge 用于最终完成 delete 和 update 操作。这样设计是因为 InnoDB 存储引擎支持 MVCC，所以记录不能在事务提交时立即进行处理。这时其他事物可能正在引用这行，故 InnoDB 存储引擎需要保存记录之前的版本。而是否可以删除该条记录通过 purge 来进行判断。若该行记录已不被任何其他事务引用，那么就可以进行真正的 delete 操作。可见，purge 操作是清理之前的 delete 和 update 操作，将上述操作“最终”完成。而实际执行的操作为 delete 操作，清理之前行记录的版本。

delete 和 update 操作可能并不直接删除原有的数据。例如，

DELETE FROM t WHERE a=1;
1

表 t 上列 a 有聚集索引，列 b 上有辅助索引。对于上述的 delete 操作，仅是将主键列等于 1 的记录 delete flag 设置为 1，记录并没有被删除，即记录还是存在于 B+树中。其次，对辅助索引上 a 等于 1，b 等于 1 的记录同样没有做任何处理。而真正删除这行记录的操作其实被“延时”了，最终在 purge 操作中完成。

# 17.二进制和事务提交一致性?

MySQL5.6 采用了 Binary Log Group Commit (BLGC)。 MySQL5.6 BLGC 的实现方式是将事务提交的过程分为几个步骤，在 MySQL 数据库上层进行提交时首先按顺序将其放入一个队列中，队列中的第一个事务称为 leader，其他事务称为 follower，leade 控制着 follower 的行为。BLGC 的步骤分为以下三个阶段:

  • Flush 阶段，将每个事务的二进制日志写入内存中。

  • Sync 阶段，将内存中的二进制日志刷新到磁盘，若队列中有多个事务，那么仅一次 fsync 操作就完成了二进制日志的写入，这就 BLGC。

  • Commit 阶段，leader 根据顺序调用存储引擎层事务的提交 InnoDB 存储引擎本就支持 group commit，因此修复了原先由于 prepare_commit_mutex 导致 group commit 失效的问题。

image-20220827155017240

因为备份及恢复的需要，例如通过工具 xtrabackup 或者 ibbackup 进行备份，并用来建立 replication，如下图所示。

image-20231022231449508

可以看到若通过在线备份进行数据库恢复来重新建立 replication，事务 T1 的数据会产生丢失。因为在 InnoDB 存储引擎层会检测最后一次的事务 T3 在上下两层都完成了提交，不需要再进行恢复，故认为之前的 T1，T2 也都完成了提交。

因此通过锁 prepare_commit_mutex 以串行的方式来保证顺序性，然而这会使 group commit 无法生效

# 18.InnoDB 的后台线程?

核心线程如下：

  • Master Thread：是一个非常核心的后端线程，主要负责将缓冲池中的数据异步刷新到磁盘中，保证数据一致性。包括脏页的刷新、insert buffer、undo 页的回收等。

  • IO Thread： InnoDB 引擎使用了大量的异步 IO 来处理 写 IO 请求，这样可以极大的提高数据库的性能。而 IO Thread 线程主要就是这些 IO 请求的一个回调处理。

  • Purge Thread：当事务被提交之后，用于回收可能不再需要的 undo log 所使用的页。

  • Page Cleaner Thread：为了提高 InnoDB 引擎的性能，在 1.2x 版本引入该线程，主要用于将之前版本中脏页的刷新操作放入单独线程，目的为了减轻原 Master Thread 线程的压力。

# 19.innodb 内存分配?

读取数据是基于页的，会首先判断页是否存在缓冲池中，如果存在，直接使用，不存在从磁盘读取.

  • 可以有多个缓冲池实例，默认为 1
  • 使用 check point 机制刷新到磁盘

image-20231022231503784

  • 缓冲池
    • 数据页
    • 索引页
    • 插入缓冲
    • 锁信息
    • 自适应哈希索引
    • 数据字典信息
  • 重做日志缓冲
  • 额外内存池:缓冲控制对象等需要从额外内存池分配内存.

# 20.LRU、Free、Flush 区别?

  • LRU List(LRU 列表):在缓冲池中，使用了 LRU 算法，存储缓冲池中的页，缓冲池中页的大小默认为 16kb，但是并不是插入到首位置，而是有个 midpoint 的概念，使用 innodb_old_blocks_pct 参数进行设置，默认为 37，插入在距离末尾 37%的位置，midpoint 之后的数据为 old 数据，之前的位置为 new 数据，也是热点数据.这样做的优点是防止热点数据被刷出缓存池.并且通过 innodb_old_blocks_time 参数，表示等待多久加入到缓冲池的热端.同样是防止热点数据不被刷出.
  • Free List:刚启动的时候，LRU 列表是空的，页是存放在 free 列表中的，需要时从 free 列表划分到 LRU 列表.
  • Flush list:脏页列表，缓存行中的页数据和磁盘不一致.脏页既存在 Flush 列表，也存在于 LRU 列表，LRU 列表用于页的可用性，Flush 列表用于刷新到磁盘.

# 21.热点数据不被冲掉?

针对全表扫描时，短时间内访问大量使用频率非常低的页面情况的优化，在进行全表扫描时，虽然首次被加载到 Buffer Pool 的页被放到了 old 区域的头部，但是后续会被马上访问到，每次进行访问的时候又会把该页放到 young 区域的头部，这样仍然会把那些使用频率比较高的页给顶下去。全表扫描有一个特点，那就是它的执行频率非常低，谁也不会没事儿老在那写全表扫描的语句玩，而且在执行全表扫描的过程中，即使某个页面中有很多条记录，也就是去多次访问这个页面所花费的时间也是非常少的。

所以我们只需要规定，在对某个处在 old 区域的缓存页进行第一次访问时就在它对应的控制块中记录下来这个访问时间，如果后续的访问时间与第一次访问的时间在某个时间间隔内，那么该页面就不会被从 old 区域移动到 young 区域的头部，否则将它移动到 young 区域的头部。上述的这个间隔时间是由系统变 innodb_old_blocks_time 控制的.这个 innodb_old_blocks_time 的默认值是 1000，它的单位是毫秒，也就意味着对于从磁盘上被加载到 LRU 链表的 old 区域的某个页来说，如果第一次和最后一次访问该页面的时间间隔小于 1s (很明显在一次全表扫描的过程中，多次访问一个页面中的时间不会超过 1s)，那么该页是不会被加入到 young 区域的.如果我们把 innodb_old_blocks_time 的值设置为 0，那么每次我们访问一个页面时就会把该页面放到 young 区域的头部。

# 22.压缩页的表?

首先，在 unzip LRU 列表中对不同压缩页大小的页进行分别管理。其次，通过伙伴算法进行内存的分配。例如对需要从缓冲池中申请页为 4KB 的大小，其过程如下:

  1. 检查 4KB 的 unzip_LRU 列表，检查是否有可用的空闲页;

  2. 若有，则直接使用;

  3. 否则，检查 8KB 的 unzip_LRU 列表;

  4. 若能够得到空闲页，将页分成 2 个 4KB 页，存放到 4KB 的 unzip_LRU 列表;

  5. 若不能得到空闲页，从 LRU 列表中申请一个 16KB 的页，将页分为 1 个 8KB 的页、2 个 4KB 的页，分别存放到对应的 unzip_LRU 列表中。

# 23.什么是 checkpoint?

为了避免发生数据丢失的问题，当前事务数据库系统普遍都采用了 Write Ahead log 策略即当事务提交时，先写重做日志，再修改页。当由干发生宕机而导致数据丢失时，通过重做日志来完成数据的恢复。这也是事务 ACID 中 D(Durability 持久性)的要求。

checkpoint 的作用

  • 缩短数据库的恢复时间;
  • 缓冲池不够用时，将脏页刷新到磁盘;Lru 列表不够用时，强制刷新脏页
  • 重做日志不可用时，刷新脏页。

对于 InnoDB 存储引擎而言，其是通过 LSN(Log Sequence Number)来标记版本的。而 LSN 是 8 字节的数字，其单位是字节。每个页有 LSN，重做日志中也有 LSN，Checkpoint 也有 LSN。

有两种 Checkpoint，分别为:

  • Sharp Checkpoint
  • Fuzzy Checkpoint

Sharp Checkpoint 发生在数据库关闭时将所有的脏页都刷新回磁盘，这是默认的工作方式，即参数 innodb_fast_shutdown=1

但是若数据库在运行时也使用 Sharp Checkpoint，那么数据库的可用性就会受到很大的影响。故在 InnoDB 存储引擎内部使用 Fuzzy Checkpoint进行页的刷新，即只刷新一部分脏页，而不是刷新所有的脏页回磁盘。 在 InnoDB 存储引擎中可能发生如下几种情况的 Fuzzy Checkpoint:

  • Master Thread Checkpoint 固定频率刷新到磁盘

  • FLUSH LRU LIST Checkpoint 缓存池中页的数量不足

  • Async/Sync Flush Checkpoint 保证重做日志的循环使用

  • Dirty Page too much Checkpoint 脏页太多，innodb_max_dirty_pages_pct，默认 90%

# 24.为什么读小数据也是 16kb?

MySQL中执行一条SQL语句，相应表数据的读写都是由存储引擎去做（更新数据、查询数据）。

在这个过程，存储引擎需要决策一些事情

  • 数据是从内存查还是从硬盘查
  • 数据是更新在内存，还是硬盘
  • 内存的数据什么时候同步到硬盘

image-20230920231058203

image-20230920231111813

InnoDB存储引擎在内存中有两个非常重要的组件，分别是缓冲池（Buffer Pool）和重做日志缓存（redo log buffer）。

缓冲池（Buffer Pool)里面会缓存很多的数据，比如数据页、索引页、锁信息等等。MySQL 表数据是以页为单位，你查询一条记录，会从硬盘把一页的数据加载出来，加载出来的数据叫数据页，会放入到 Buffer Pool中。后续的查询先从 Buffer Pool中找，没有命中再去硬盘加载，减少硬盘 IO 开销，提升性能。更新表数据的时，如果Buffer Pool里命中数据，就直接在Buffer Pool 里更新。

# 25.NULL 值问题

在 MySQL 中，创建二级索引时，索引树上通常不会包含 NULL 值。索引树的目的是加速查询，而 NULL 值并不提供有用的信息来加速查询过程。

如果您在一个可为空（nullable）的字段上创建了二级索引，并且该字段的某些行具有 NULL 值，这些 NULL 值的记录不会在索引树中显示。当执行查询时，如果您要查找具有 NULL 值的记录，MySQL 将不会使用该二级索引，而是需要执行全表扫描来找到包含 NULL 值的记录。

这是因为在 MySQL 中，对于包含 NULL 值的记录，查询优化器通常认为它们的数量相对较少，通过全表扫描来定位这些记录可能更为高效。因此，当您执行查询条件包含对 NULL 值的判断时，MySQL 可能会选择进行全表扫描。

如果您经常需要查询具有 NULL 值的记录，并且性能成为问题，您可以考虑使用其他技术，如使用一个额外的标志字段来指示是否为 NULL，或者使用特殊的值来替代 NULL 值，以便在二级索引中进行索引。

需要注意的是，MySQL 的行为在不同的版本和配置下可能会有所不同。因此，建议在具体的环境中进行测试和评估，以确定在包含 NULL 值的字段上使用二级索引时的实际性能和行为。

# 26.NULL 值是怎么存放的？

MySQL 的 Compact 行格式中会用「NULL值列表」来标记值为 NULL 的列，NULL 值并不会存储在行格式中的真实数据部分。

NULL 值列表会占用 1 字节空间，当表中所有字段都定义成NOT NULL，行格式中就不会有 NULL值列表，这样可节省 1 字节的空间。

在 MySQL 数据库中，NULL 值是一种特殊的值，用于表示缺少值或未知值。在内部，MySQL 使用一种称为 "NULL bitmap" 或 "null bitmap" 的机制来存储 NULL 值。

每个存储在表中的行都有一个对应的 NULL 位图，它是一个位数组，其中的每个位对应于该行中的一个列。如果列的值为 NULL，则对应的位被设置为 1；如果列的值不为 NULL，则对应的位被设置为 0。这样，数据库系统可以有效地跟踪每列是否包含 NULL 值。

这种位图的存储方式使得数据库可以更有效地使用存储空间，并且能够快速地进行 NULL 值的检索和比较。当查询需要检查 NULL 值时，数据库系统可以直接引用 NULL 位图，而无需实际检查每个列的值。

需要注意的是，NULL 值在数据库中有特定的语义，它表示缺少值或未知值，与空字符串或零值不同。在查询中，可以使用 IS NULL 或 IS NOT NULL 来检查 NULL 值。

MySQL 使用 NULL 位图来存储 NULL 值，这种机制在数据库中有效地管理和表示缺少值。

# 27.is null 查询问题

select age from table where age is null;
1

在 MySQL 中，当使用select age from table where age is null;这样的查询语句时，如果"age"列没有被设置为索引，那么 MySQL 通常不会使用索引来执行这个查询。原因是，对于包含IS NULL或IS NOT NULL条件的查询，MySQL 的优化器通常认为全表扫描是更有效的方式。

当没有索引可用时，MySQL 需要扫描整个表来找到满足条件的行，然后返回"age"列的值。如果"age"列被设置为索引，MySQL 可能会使用索引来快速定位满足条件的行，这样可以大大加快查询速度。

但是对于"age is null"这种条件，即使有索引，MySQL 通常也不会使用它。这是因为 NULL 值在数据库中是一种特殊的值，索引的存储方式会导致难以有效地使用索引来加速这类查询。因此，MySQL 倾向于执行全表扫描来查找 NULL 值，而不是使用索引。

如下图所示，通过 explain 查询出来的 type 为 ALL，ALL: 表示 MySQL 将执行全表扫描，将遍历表中的每一行来匹配查询条件。这通常发生在没有合适的索引可供优化查询时，或者查询条件无法使用索引优化。

image-20230802094120657

上次更新: 11/2/2024, 2:13:44 PM

02-MySQL 文件 →